/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOOrderDetail;
import DTO.DTOStatisticSold;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALOrderDetail {
    ConnectionDB conn;
    
    public DALOrderDetail() {
        conn = new ConnectionDB();
    }
    
    //method get detail of order with id
    public ArrayList<DTOOrderDetail> getOrderDetailsbyId(String OrderId){
        try{
            ArrayList<DTOOrderDetail> listOrdDetail = new ArrayList<>();
            String querySelect = "select * from `order_detail` where order_id = '" + OrderId + "'";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next()){
                listOrdDetail.add(new DTOOrderDetail(rs));
            }
            return listOrdDetail;
        }
        catch(Exception ex){
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method get all order has execute export
    public ArrayList<DTOOrderDetail> getOrderHasExport()
    {
        try{
            ArrayList<DTOOrderDetail> listOrdHasExport = new ArrayList<>();
            String querySelect = "SELECT * "
                                + "FROM `order` o, `order_detail` d "
                                + "WHERE o.order_id = d.order_id AND order_state = 2";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listOrdHasExport.add(new DTOOrderDetail(rs));
            }
            return listOrdHasExport;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method statistic number of product has sold
    public ArrayList<DTOStatisticSold> statisticProductHasSold(int month, int year, String order)
    {
        try{
            ArrayList<DTOStatisticSold> listStatistic = new ArrayList<>();
            String querySelect = "SELECT p.product_id, p.product_name, SUM(d.quantity) as 'quantity' " +
                                "FROM `product` p, `order_detail` d, `order` o " +
                                "WHERE p.product_id = d.product_id AND o.order_id = d.order_id AND o.order_state = 2 AND MONTH(o.date_export) like '" + month + "' " +
                                "AND YEAR(o.order_datecreated) like '" + year + "' " +
                                "GROUP BY p.product_id ";
            if(!"".equals(order)){
                querySelect += "ORDER BY quantity " + order + " "
                            + "LIMIT 10 ";
            }
            
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listStatistic.add(new DTOStatisticSold(rs));
            }
            return listStatistic;
        }
        catch(Exception ex){
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method create order detail
    public boolean createOrdDetail(DTOOrderDetail dtoDetail)
    {
        try{
            String queryInsert = "insert into `order_detail`(`order_id`, `product_id`, `quantity`) values(?, ?, ?)";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryInsert);
            pstmt.setString(1, dtoDetail.getDtoOrder().getOrderId());
            pstmt.setInt(2, dtoDetail.getDtoPro().getProductID());
            pstmt.setInt(3, dtoDetail.getQuantity());
            return pstmt.executeUpdate() == 1;
        }
        catch(Exception ex)
        {
            System.err.println(ex);
            return false;
        }
    }
    
    //method delete order_detail
    public boolean deleteOrdDetail(String orderId)
    {
        try{
            String queryDel = "DELETE FROM `order_detail` WHERE `order_id` = '"+orderId+"'";
            return conn.exeUpdate(queryDel);
        }
        catch(Exception ex)
        {
            System.err.print(ex);
            return false;
        }
    }
}
